# -*- coding: utf-8 -*-
import pymongo
client = pymongo.MongoClient('127.0.0.1')
db = client.dmm
import re
import MySQLdb
import MySQLdb.cursors
conn = MySQLdb.connect("127.0.0.1","root","123456789", "shantianjun",charset="utf8mb4",cursorclass=MySQLdb.cursors.DictCursor)
sql = "select * from dmm_play"
cursor = conn.cursor()
cursor.execute(sql)
play = cursor.fetchallDict()
cursor.execute("select * from dmm_tag")
tag = cursor.fetchallDict()
cursor.execute("select * from dmm_av")
av = cursor.fetchallDict()
avs = {}
tags = {}
plays = {}
for yy in av:
    avs[yy["url"]] = yy
    print yy["id"]
for yy in tag:
    tags[yy["name"]] = yy
for yy in play:
    plays[yy["name"]] = yy

tagList = set()
playList = set()
tmp = db.girl.find({"date":"2018-02-15"})
for y in tmp:
        if not y.get("title"):
            continue
        # sql = "select id from dmm_av where url = '%s'" % y["url"].replace("http://www.dmm.co.jp","")
        # cursor.execute(sql)
        # aid = cursor.fetchone()["id"]
        aid = avs[y.get("url").replace("http://www.dmm.co.jp","")]["id"]
        if not aid:
            print "continue"
            continue
        for yyy in y.get("cate"):
            tagList.add((aid,tags[yyy]["id"]))
        for yyy in y.get("play"):
            if not plays.get(yyy):
                print y
                continue
            playList.add((aid,plays.get(yyy)["id"]))

print "kk"
sql = "insert into dmm_av_play (aid,pid) values (%s,%s)"
cursor.executemany(sql,playList)
sql = "insert into dmm_av_tag (aid,tid) values (%s,%s)"
cursor.executemany(sql,tagList)
print sql

cursor.close()
conn.commit()
# insert info cate (name) select select distinct(cate) from stores where cate <> ""
